驱动程序

npm i -D mysql2

const mysql = require('mysql2');

// 创建一个数据库连接
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'user',
});

connection.query(
    "INSERT INTO `user`.`account`(id, `name`, pwd, logonTime, sex) VALUES('4', 'bozai', '123456', '2020-10-2', 0);",
    (err, res) => {
        console.log(res);
    }
)

connection.end();  // 断开连接

异步方式

const mysql = require('mysql2/promise');

async function test () {
    const connection = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '123456',
        database: 'user',
    });
    
    const [results] = await connection.query("UPDATE `user`.`account` SET `pwd` = '654321' WHERE id = 2;");
    connection.end();
}
test();

防 SQL 注入

• 攻击者在 web 应用程序语句的结尾上添加额外的SQL语句，实现非法操作。

const mysql = require('mysql2/promise');

async function test (id) {
    const connection = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '123456',
        database: 'user',
        multipleStatements: true,  // 允许执行多条 SQL 语句
    });
    
    let sql = "SELECT * FROM `user`.`account` WHERE id = ?;"
    const [results] = await connection.execute(sql, [id]);
    console.log(results);
    connection.end();
}
test("''; DELETE FROM accunt WHERE `name` = 'bozai';");

连接池

数据库连接是一种关键的、有限的、昂贵的资源，直接影响到整个应用程序的伸缩性和健壮性。

• 多数用户请求时为防止服务器卡顿，设定最大连接数，让额外的用户进行排队，服务器空闲时即发送请求。

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'user',
    connectionLimit: 10,  // 最大连接数
    waitForConnections: true,  // 是否等待
    queueLimit: 0,  // 队列排队数量，0 表示无限制
});
async function test (str) {
    let sql = "SELECT * FROM `user`.`account` WHERE `name` LIKE CONCAT('%', ?, '%');";  // concat 函数字符连接
    const [results] = await pool.execute(sql, [str]);
    console.log(results);
    pool.end();
}
test('y');